package unq.tpi.persistencia.performanceEj.daos;

import java.util.List;

import org.hibernate.classic.Session;
import org.hibernate.criterion.Restrictions;

import unq.tpi.persistencia.performanceEj.model.Department;
import unq.tpi.persistencia.performanceEj.model.Employee;
import unq.tpi.persistencia.util.SessionManager;

public class DepartmentDAO {

	public Department getByName(final String name) {
		Session session = SessionManager.getSession();
		return (Department) session
				.createQuery("from Department where name = :name")
				.setParameter("name", name).uniqueResult();
	}

	public Department getByCode(String num) {
		Session session = SessionManager.getSession();
		return (Department) session.createQuery(
				"select d from Department d join fetch d.employees e join fetch e.salaries s join fetch e.titles t where d.number = :num and s.to = '9999-01-01' ")
				.setString("num", num).uniqueResult();
	}
	
	@SuppressWarnings("unchecked")
	public List<Employee> getEmployees(String num){
		Session session = SessionManager.getSession();
		return (List<Employee>) session.createQuery("SELECT d.employees FROM Department d "
				+ "WHERE d.number = :num").setString("num", num)
					.list();
		
	}

	public double getTotalSalaries(String num){
		Session session = SessionManager.getSession();
		return (Double) session.createQuery("SELECT SUM(e) FROM Department d LEFT JOIN Employee e "
				+ "WHERE d.number = :num").setString("num", num)
					.uniqueResult();
	}
	
	@SuppressWarnings("unchecked")
	public List<Department> getAll() {
		Session session = SessionManager.getSession();
		return session.createCriteria(Department.class).list();
	}
	
	@SuppressWarnings("unchecked")
	public List<Department> getListadoDeptos(){
		Session session = SessionManager.getSession();
		return (List<Department>) session.createQuery("SELECT DISTINCT d FROM Department d LEFT JOIN "
				+ "d.managers").list();
	}

}
